SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[Prop1Q13_Exits]
AS
	BEGIN
		DECLARE	@dbname VARCHAR(50)
		DECLARE	@FundID VARCHAR(10)
		DECLARE	@NAVDate DATETIME
 
 
		SET @FundID = 'FSIC'
		SET @NAVDate = '3/29/2013'
		
		DECLARE @ExportId INT  = ( SELECT
												MAX(e.ExportId)
											FROM
												FSFundModel_Valcomm.Reporting.Export e
											WHERE
												e.FundId = @FundID
												AND e.NAVDate = @NAVDate
												 )
 													
		SELECT
			i.InstrumentId
		--, fct.FundCreditTransType -------------------------------------------
		, iss.IssuerName
		, i.InstrName
		, id.InstrClass
		, p.PortfolioName
		, [ass].[ActivityType]
		, t.TradeAmount
		, [ass].[TradePrice]
		, [ass].[ActivityDate]
		, prevDate.GAAPYldPurchPrc AS 'TD-1GAAPYieldAtPurch'
		, CASE	WHEN ah.[NBSReason] IS NOT NULL THEN 1
						ELSE 0
			END AS IsProprietary
		, [ah].[NBSReason]
		, [ah].[portfoliopurchase]
		, [a].[TotalCredit]
		, iss.TTMEBITDA
		, i.MaturityDate
		, prevDate.AmortCost 'AmortizedCostBeforeTrade'
		, t.AmortCost 'AmortCostTrade'
		, a.IsActiveEndOfDay
		, CASE	WHEN a.[TotalCredit] IS NOT NULL THEN 1
						ELSE 0
			END AS IsUnfunded
		, [a].[AvailCredit]
		, a.GAAPYldPurchPrc
		, a.AmortCost
		, prevDate.AmortCostPctPar 'TD-1AmortCostPctPar'
		FROM
			FSFundModel_Valcomm.Reporting.Asset a
			INNER JOIN  [ReportingScripts].[Valcomm].[AssetHelper](@ExportId) ah ON [a].[AssetIntrnlId] = [ah].[AssetIntrnlId]
			INNER JOIN FSFundModel_Valcomm.Reporting.Instrument i ON i.ExportId = a.ExportId
																						AND a.InstrumentId = i.InstrumentId
			INNER JOIN FSFundModel_Valcomm.Reporting.Issuer iss ON iss.ExportId = a.ExportId
																						AND i.IssuerId = iss.IssuerId
			INNER JOIN FSFundModel_Valcomm.Reporting.InstrumentDefinition id ON i.InstDefId = id.InstDefId
																						AND id.ExportId = a.ExportId
			INNER JOIN FSFundModel_Valcomm.Reporting.FundIssuer fi ON a.ExportId = fi.ExportId
																						AND fi.IssuerId = iss.IssuerId
			INNER JOIN FSFundModel_Valcomm.Reporting.Portfolio p ON a.ExportId = p.ExportId
																						AND a.PortfolioId = p.PortfolioId
			INNER JOIN FSFundModel_Valcomm.Reporting.Fund f ON a.ExportId = f.ExportId
			INNER JOIN FSFundModel_Valcomm.Reporting.Trade t ON a.ExportId = t.ExportId
																						AND a.InstrumentId = t.InstrumentId
																						AND a.LotId = t.LotId
																						AND a.PortfolioId = t.PortfolioId
				INNER JOIN  [FSFundModel_VALCOMM].[Reporting].[ActivitySummary] ass ON [ass].[ActivitySummaryId] = [t].[ActivitySummaryId]
				AND [ass].[ExportId] = t.[ExportId]
				--LEFT JOIN  [FSFundModel_VALCOMM].[Reporting].[FundCreditTrans] fct ON [fct].[ExportId] = [a].[ExportId]
				--AND [a].[InstrumentId] = [fct].[InstrumentId] AND [a].[PortfolioId] = [fct].[PortfolioId] AND [a].[LotId] = [fct].[LotId]

			--OUTER APPLY ( SELECT
			--								fi.*
			--							, ft.FundCreditTransType
			--							, ft.TransAmt
			--							, ft.TransDate
			--							FROM
			--								FSFundModel_VALCOMM.Reporting.FundCreditTrans ft
			--							WHERE
			--								ft.ExportId = a.ExportId
			--								AND [a].[InstrumentId] = [ft].[InstrumentId] AND [a].[PortfolioId] = [ft].[PortfolioId] AND [a].[LotId] = [ft].[LotId]
											
			--								) fci
			OUTER  APPLY ( SELECT
												pd.*
											FROM
												Valcomm.getAssetByNAVDateOffset([ass].[ActivityDate],
																						-1,
																						t.PortfolioId,
																						t.InstrumentId,
																						t.LotId) pd --(f.PrevNAVDate, @FundID) pd
											) prevDate
		WHERE
			a.ExportId = @ExportId
			AND ([ass].[ActivityDate] >= '1/1/2013'
			AND [ass].[ActivityDate] <= @NAVDate )
			AND [a].[FundCreditInstrumentId] IS NULL 
		ORDER BY
			id.SortOrder
		, [ass].[ActivityDate] ASC 






	END

GO
